package com.guchenbo.spark.sql

import org.apache.spark.sql.{SaveMode, SparkSession}

/**
 * @author guchenbo
 * @date 2022/2/17
 */
object PostgreSQL {

  def main(args: Array[String]): Unit = {
    var spark = SparkSession.builder().master("local").appName("postgreSQL")
      .enableHiveSupport()
      .getOrCreate()

    var reader = spark.read.format("jdbc")
      .option("url", "jdbc:postgresql://10.58.11.12:5432/pgtest0611")
      .option("user", "pgadmin")
      .option("password", "pgadmin123")
    //      .option("driver","")

    var sql = "with view_alias as (select * from sales) insert overwrite table sales_insert select * from (select * from ( select * from view_alias ) Table_0) dims"

    //    reader.option("dbtable", s"($sql) as sub")

    reader.option("dbtable", "public.sales")
    var df = reader.load()
    df.show()

    val df2 = reader.option("dbtable","test01").load()
df2.show()
    df.createOrReplaceTempView("sales")

    df2.createOrReplaceTempView("test02")

    spark.sql("select * from sales").show()
    spark.sql("select * from test02").show()

spark.sql("create table if not exist testinsert stored as parquet select * from test02")
//    spark.sql(sql)

//    spark.sql("select * from sales_insert").show()

        sql = "select stack(6,'10','single_value','id_no','0.5',null,'200','bin1','1','10.0',null,null,'(0,1]','range','id_no','0.2','50','250','bin2','2','20.0','0','1','(1,999999999)','range','id_no','0.3','50','250','bin3','3','20.0','1','999999999','(-999999999,1]','range','f1','0.5','100','200','bin1','1','10.0','-999999999','1','(1,10]','range','f1','0.2','50','250','bin2','2','20.0','1','10','(10,999999999)','range','f1','0.3','50','250','bin3','3','20.0','10','999999999') as (bin_range,bin_range_type,variable_name,train_percent,p_sample,n_sample,bin_name,bin_no,score,low,up)"
//        df = reader.option("dbtable",s"($sql) as sub").load()
//        df.show()

    spark.sql(sql).show()
    df.write
//
//    var csvDf = spark.read.format("csv").option("header", "true").csv("file:////Users/guchenbo/Downloads/model_report_test2.csv")
//    csvDf.show()
//
//    csvDf.write.mode(SaveMode.Overwrite).saveAsTable("turing.model_report_psi_test_df")

  }
}
